/*******************************************************************************************/
/*   Program       : IBES_Sample.sas  
/*   Author  : Denys Glushkov, WRDS  
/*   Date Created  : Feb 2008  
/*   Last Modified : June 2009
/*     
/*   Description: Extract estimates and link to actuals     
/*     
/* Macro IBES_SAMPLE extracts the estimates from the IBES Unadjusted file based on         */
/* the user-provided input, links them to actuals, puts estimates and actuals on the       */
/* same basis by adjusting for stock splits using CRSP adjustment factor and calculates    */
/* the median of analyst forecasts made in the 90 days prior to the earnings announcement  */
/* date. 																			       */
/*******************************************************************************************/


%let detu = detu_epsus; /* Unadjusted Detail history with EPS measure in the US region*/
%let actu = actu_epsus; /* Unadjusted Actual with EPS measure in the US region*/


%MACRO IBES_SAMPLE (infile=, ibes1_where=, ibes2_where=, ibes_var=);

proc sql; 
  create table ibes (drop=measure)
  as select *
  from ibes.&detu (&ibes1_where keep=&ibes_var) as a,       /* ibes1_where and ibes_var are specified*/
       &infile as b    /* prior to invoking IBES_SAMPLE*/
  where a.ticker=b.ticker;
quit;

proc sort data = ibes; by ticker fpedats fpi estimator analys anndats revdats; where value ne .; * and analys ne 000000; *; run;

/*Select the last estimate for a firm within broker-analyst group*/     
data ibes; set ibes; by ticker fpedats fpi estimator analys;
  if last.analys;
  if fpi in ('1','2','3','4') then fiscalp = 'ANN'; else fiscalp = 'QTR';
run;

/*How many estimates are reported on primary/diluted basis?*/
proc sql; 
  create table ibes 
    as select a.*, sum(pdf='P') as p_count, sum(pdf='D') as d_count
    from ibes as a
    group by ticker, fpedats, fpi;

/* a. Link unadjusted estimates with unadjusted actuals and CRSP permnos  */
/* b. Adjust report and estimate dates to be CRSP trading days      */
  create table ibes1 (&ibes2_where)
    as select a.*, b.anndats as repdats, b.pends, b.value as act, c.permno,
    case when weekday(a.anndats)=1 then intnx('day',a.anndats,-2)      /*if sunday move back by 2 days;*/
   when weekday(a.anndats)=7 then intnx('day',a.anndats,-1) else a.anndats   /*if saturday move back by 1 day*/
    end as estdats1,
    case when weekday(b.anndats)=1 then intnx('day',b.anndats,1)      /*if sunday move forward by 1 day  */
   when weekday(b.anndats)=7 then intnx('day',b.anndats,2) else b.anndats   /*if saturday move forward by 2 days*/
    end as repdats1
    from ibes as a, ibes.&actu as b, ibes.iclink as c
    where a.ticker=b.ticker and a.fpedats=b.pends and a.usfirm=b.usfirm and a.fiscalp = b.pdicity and b.measure='EPS' and a.ticker=c.ticker and c.score in (0,1,2); 

/*   Making sure that estimates and actuals are on the same basis
      */
/*   1. retrieve CRSP cumulative adjustment factor for IBES report and estimate dates       */
  create table adjfactor
    as select distinct a.*
    from crsp.dsf (keep=permno date cfacshr prc) as a, ibes1 as b
    where a.permno=b.permno and (a.date=b.estdats1 or a.date=b.repdats1);
  
/*      2.if adjustment factors are not the same, adjust the estimate to be on the same basis with the actual   */
  create table ibes1
    as select distinct a.*, b.est_factor, c.rep_factor, abs(c.prc) as price, 
      case when (b.est_factor ne c.rep_factor) and missing(b.est_factor)=0 and missing(c.rep_factor)=0
      then (rep_factor/est_factor)*value else value end as new_value
    from ibes1 as a, 
      adjfactor (rename=(cfacshr=est_factor)) as b, 
      adjfactor (rename=(cfacshr=rep_factor)) as c 
      where (a.permno=b.permno and a.estdats1=b.date) and
    (a.permno=c.permno and a.repdats1=c.date);
quit;

/* Make sure the last observation per analyst is included
      */
proc sort data=ibes1; 
  by ticker fpedats fpi estimator analys anndats revdats;
run;

data ibes1; set ibes1; by ticker fpedats fpi estimator analys;
  if last.analys;
  scaled_forecast = new_value/price;
run;

/* Compute the median forecast based on estimates in the 90 days prior to the report date    */
proc means data=ibes1 noprint; by ticker fpedats fpi;
  var /*value*/ new_value scaled_forecast;       /* new_value is the estimate appropriately adjusted   */
  output out= medest (drop=_type_ _freq_)   /* to be on the same basis with the actual reported earnings */
    median=medest mean=meanest n=numest std=dispersion dispersion_scaled;
run;

/* Merge median estimates with ancillary information on permno, actuals and report dates      */
/* Determine whether most analysts are reporting estimates on primary or diluted basis  */
/* following the methodology outlined in Livnat and Mendenhall (2006)       */
proc sql; 
  create table medest 
  as select distinct a.*, b.repdats, b.act, b.permno, b.price,
  case when p_count>d_count then 'P' 
       when p_count<=d_count then 'D' 
  end as basis     
  from medest as a left join ibes1 as b
  on a.ticker=b.ticker and a.fpedats=b.fpedats and a.fpi=b.fpi;
quit;

%MEND;


/***********************************************************************************************/
/* Program      : SUE.sas                                                                      */
/* Author       : Denys Glushkov, WRDS                                                         */
/* Date Created : Feb 2008                                                                     */	
/* Last Modified: Jun 2009								       */
/*                                                                                             */
/* Description: 3 Methods of calculating standardized earnings surprises                       */
/*                                                                                             */
/* Macro SUE calculates standardized earnings surprises using 3 (three) methods considered     */
/* by LM (2006). Method 1 assumes a rolling seasonal random walk model. Method 2 excludes      */
/* "special items" from the Compustat Data. In these two methods, if most analyst forecasts of */
/* EPS are based on diluted (primary) EPS, Macro uses Compustat's diluted (basic) figures      */
/* Method 3 is based solely on IBES median estimates/actuals and does not use Compustat data   */
/***********************************************************************************************/

%MACRO SUE (method=, input=);
/* Process Compustat Data on a seasonal year-quarter basis*/
%local i;
%do i=1 %to 4;
proc sort data=&input (where=(fqtr=&i)) out=qtr;
	by gvkey fyearq fqtr;
run;

data qtr; set qtr;
	by gvkey fyearq;
	%if &method=1 %then 
	 %do;
	 	lageps_p=lag(epspxq);lageps_d=lag(epsfxq);lagadj=lag(ajexq);lagfqtr=lag(datafqtr);
		if first.gvkey then do; lageps_p=.;lageps_d=.;lagadj=.;end;
		select (basis);
	 	when ('P') do; actual=epspxq/ajexq; expected=lageps_p/lagadj;end;
		when ('D') do; actual=epsfxq/ajexq; expected=lageps_d/lagadj;end;
		otherwise do; actual=epspxq/ajexq; expected=lageps_p/lagadj;end;
		end;
		drop lageps_p lageps_d lagadj;
		deflator=prccq/ajexq;
	%end;%else;
	%if &method=2 %then 
	 %do;
	 	lageps_p=lag(epspxq);lagshr_p=lag(cshprq);lagadj=lag(ajexq);lagfqtr=lag(datafqtr);
		lageps_d=lag(epsfxq);lagshr_d=lag(cshfdq);lagspiq=lag(spiq);
		if first.gvkey then do; lageps_p=.;lageps_d=.;lagshr_p=.;
								lagshr_d=.;lagadj=.;lagspiq=.;end;
		select (basis);
   		when ('P') do; actual=sum(epspxq,-0.65*spiq/cshprq)/ajexq; expected=sum(lageps_p,-0.65*lagspiq/lagshr_p)/lagadj;end;
		when ('D') do; actual=sum(epsfxq,-0.65*spiq/cshfdq)/ajexq; expected=sum(lageps_d,-0.65*lagspiq/lagshr_d)/lagadj;end;
		otherwise do; actual=sum(epspxq,-0.65*spiq/cshprq)/ajexq; expected=sum(lageps_p,-0.65*lagspiq/lagshr_p)/lagadj;end;
		end;
		drop lageps_p lagshr_p lagadj lageps_d lagshr_d lagspiq;
		deflator=prccq/ajexq;
		%end;%else;
	%if &method=3 %then 
	 %do;
		actual=act;
		expected=medest;
		deflator=prccq;
	 %end;
	%if &method=4 %then 
	 %do;
	 	lageps_p=lag(epspxq);lageps_d=lag(epsfxq);lagadj=lag(ajexq);lagfqtr=lag(datafqtr);
		if first.gvkey then do; lageps_p=.;lageps_d=.;lagadj=.;end;
		select (basis);
	 	when ('P') do; actual=epspxq/ajexq; expected=lageps_p/lagadj;end;
		when ('D') do; actual=epsfxq/ajexq; expected=lageps_d/lagadj;end;
		otherwise do; actual=epspxq/ajexq; expected=lageps_p/lagadj;end;
		end;
		drop lageps_p lageps_d lagadj;
		deflator=prccq/ajexq;
	 %end;
	%if &method=5 %then 
	 %do;
		actual=act;
		expected=meanest;
		deflator=prccq;
	 %end;
	%if &method=6 %then 
	 %do;
		actual=act;
		expected=medest;
		deflator=dispersion;
	 %end;
	sue&method=(actual-expected)/deflator;
	* format sue&method percent7.4;
run;

proc append base=comp_final&method data=qtr;run;
%end;

proc sort data=comp_final&method; by gvkey fyearq fqtr;run;
%MEND;


/*******************************************************************************
Winsorize macro
Can winsorize or trim at specified percentiles;
dsetout = leave blank to overwrite dsetin
byvar   = none for no byvar (trims/winsorizes pooled sample)
type    = delete/winsor

ex: %winsor(dsetin=mydata, dsetout=mydata2, byvar=year, vars=assets earnings, pctl=0 98);
     winsorizes by year at 98%, puts resulting dataset into mydata2 
     %winsor(dsetin=mydata, vars=assets earnings, type=delete);
     trims pooled sample at 1% and 99%, puts resulting dataset back into mydata
********************************************************************************/

%macro winsor(dsetin=, dsetout=, byvar=none, vars=, type=winsor, pctl=1 99); 
%if &dsetout = %then %let dsetout = &dsetin;
%let varL=;
%let varH=;
%let xn=1; 
%do %until ( %scan(&vars,&xn)= );
    %let token = %scan(&vars,&xn);
    %let varL = &varL &token.L;
    %let varH = &varH &token.H;
    %let xn=%EVAL(&xn + 1);
%end; 

%let xn=%eval(&xn-1); 
data xtemp;
    set &dsetin;
    run; 

%let dropvar = ;
%if &byvar = none %then %do; 
    data xtemp;
        set xtemp;
        xbyvar = 1;

%let byvar = xbyvar;
%let dropvar = xbyvar; 

%end; 

proc sort data = xtemp; by &byvar;

proc univariate data = xtemp noprint; by &byvar;
    var &vars;
    output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H;

data &dsetout;
    merge xtemp xtemp_pctl;
    by &byvar;
    array trimvars{&xn} &vars;
    array trimvarl{&xn} &varL;
    array trimvarh{&xn} &varH; 
    do xi = 1 to dim(trimvars); 
        %if &type = winsor %then %do;
            if trimvars{xi} ne . then do;
              if (trimvars{xi} < trimvarl{xi}) then trimvars{xi} = trimvarl{xi};
              if (trimvars{xi} > trimvarh{xi}) then trimvars{xi} = trimvarh{xi};
            end;
        %end; 

        %else %do;
            if trimvars{xi} ne . then do;
              if (trimvars{xi} < trimvarl{xi}) then delete;
              if (trimvars{xi} > trimvarh{xi}) then delete;
            end;
        %end; 
    end;
    drop &varL &varH &dropvar xi;
    run; 
%mend winsor;

